import os
import sqlite3
import datetime
import json
from flask import Flask, request, render_template, jsonify

app = Flask(__name__)


def get_db():
    BASE_DIR = os.path.dirname(os.path.abspath(__file__))
    db_path = os.path.join(BASE_DIR, "zen.db")
    print(db_path)
    db = sqlite3.connect(db_path)
    db.row_factory = sqlite3.Row
    return db


def query_db(query, args=(), one=False):
    db = get_db()
    cur = db.execute(query, args)
    db.commit()
    rv = cur.fetchall()
    db.close()
    return (rv[0] if rv else None) if one else rv


@app.route("/", methods=["GET"])
def index():
    return render_template("index.html")


@app.route("/cpu", methods=["POST"])
def cpu():
    if request.method == "POST":
        data = {'fromDate': 20220602, 'toDate': datetime.date.today().strftime('%Y%m%d')}
        data = json.loads(request.get_data(as_text=True))
        # data = json.loads(json.dumps(request.get_data(as_text=True)))
        for key, value in data.items():
            if value == '':
                data[key] = datetime.date.today().strftime('%Y%m%d')
        for key, value in data.items():
            if type(value) == str and value != 'i':
                data[key] = value
        if data["fromDate"]>data["toDate"]:
            data["fromDate"] , data["toDate"] = data["toDate"] , data["fromDate"]

        print(type(data),data)
        res = query_db("SELECT COUNT(People_id) AS totalCount,"
                       "AVG(Score) AS ScoreAvg,"
                       "SUM(CASE WHEN Gender = 'Male' THEN 1 ELSE 0 END) AS MaleCount,"
                       "SUM(CASE WHEN Gender = 'Female' THEN 1 ELSE 0 END) AS FemaleCount,"
                       "SUM(CASE WHEN Age = 'Age18-60' THEN 1 ELSE 0 END) AS Age1860Count,"
                       "SUM(CASE WHEN Age = 'AgeLess18' THEN 1 ELSE 0 END) AS AgeLess18Count,"
                       "SUM(CASE WHEN Age = 'AgeOver60' THEN 1 ELSE 0 END) AS AgeOver60Count,"
                       "SUM(CASE WHEN Orientation = 'Side' THEN 1 ELSE 0 END) AS SideCount,"
                       "SUM(CASE WHEN Orientation = 'Back' THEN 1 ELSE 0 END) AS BackCount,"
                       "SUM(CASE WHEN Orientation = 'Front' THEN 1 ELSE 0 END) AS FrontCount,"
                       "SUM(CASE WHEN Glasses = 'Glasses: False' THEN 1 ELSE 0 END) AS GlassesFalseCount,"
                       "SUM(CASE WHEN Glasses = 'Glasses: True' THEN 1 ELSE 0 END) AS GlassesTrueCount ,"
                       "SUM(CASE WHEN Hat = 'Hat: True' THEN 1 ELSE 0 END) AS HatTrueCount ,"
                       "SUM(CASE WHEN Hat = 'Hat: False' THEN 1 ELSE 0 END) AS HatFalseCount ,"
                       "SUM(CASE WHEN HoldObjectsInFront = 'HoldObjectsInFront: False' THEN 1 ELSE 0 END) AS HoldObjectsInFrontFalseCount ,"
                       "SUM(CASE WHEN HoldObjectsInFront = 'HoldObjectsInFront: True' THEN 1 ELSE 0 END) AS HoldObjectsInFrontTrueCount ,"
                       "SUM(CASE WHEN Bag = 'ShoulderBag' THEN 1 ELSE 0 END) AS ShoulderBagCount ,"
                       "SUM(CASE WHEN Bag = 'No bag' THEN 1 ELSE 0 END) AS NobagCount ,"
                       "SUM(CASE WHEN Bag = 'HandBag' THEN 1 ELSE 0 END) AS HandBagCount ,"
                       "SUM(CASE WHEN Bag = 'Backpack' THEN 1 ELSE 0 END) AS BackpackCount ,"
                       "SUM(CASE WHEN 'Upper' = 'Upper: LongSleeve' THEN 1 ELSE 0 END) AS LongSleeveCount ,"
                       "SUM(CASE WHEN 'Upper' = 'Upper: LongSleeve UpperLogo' THEN 1 ELSE 0 END) AS LongSleeveUpperLogoCount ,"
                       "SUM(CASE WHEN 'Upper' = 'Upper: LongSleeve UpperSplice' THEN 1 ELSE 0 END) AS LongSleeveUpperSpliceCount ,"
                       "SUM(CASE WHEN 'Upper' = 'Upper: ShortSleeve' THEN 1 ELSE 0 END) AS ShortSleeveCount ,"
                       "SUM(CASE WHEN 'Upper' = 'Upper: ShortSleeve UpperLogo' THEN 1 ELSE 0 END) AS ShortSleeveUpperLogoCount ,"
                       "SUM(CASE WHEN 'Lower' = 'Lower:  LongCoat' THEN 1 ELSE 0 END) AS LongCoatCount ,"
                       "SUM(CASE WHEN 'Lower' = 'Lower:  LongCoat Trousers' THEN 1 ELSE 0 END) AS LongCoatTrousersCount ,"
                       "SUM(CASE WHEN 'Lower' = 'Lower:  LowerPattern' THEN 1 ELSE 0 END) AS LowerPatternCount ,"
                       "SUM(CASE WHEN 'Lower' = 'Lower:  Shorts' THEN 1 ELSE 0 END) AS ShortsCount ,"
                       "SUM(CASE WHEN 'Lower' = 'Lower:  Skirt&Dress' THEN 1 ELSE 0 END) AS SkirtDressCount ,"
                       "SUM(CASE WHEN 'Lower' = 'Lower:  Trousers' THEN 1 ELSE 0 END) AS TrousersCount "
                       "FROM attr_table WHERE Date BETWEEN "+str(data['fromDate'])+" AND "+str(data['toDate'])+";")  # 返回1+个数据

    return jsonify(totalCount=[x[0] for x in res],
                   ScoreAvg=[x[1] for x in res],
                   MaleCount=[x[2] for x in res],
                   FemaleCount=[x[3] for x in res],
                   Age1860Count=[x[4] for x in res],
                   AgeLess18Count=[x[5] for x in res],
                   SideCount=[x[6] for x in res],
                   BackCount=[x[7] for x in res],
                   FrontCount=[x[8] for x in res],
                   GlassesFalseCount=[x[9] for x in res],
                   GlassesTrueCount=[x[10] for x in res],
                   HatTrueCount=[x[11] for x in res],
                   HatFalseCount=[x[12] for x in res],
                   HoldObjectsInFrontFalseCount=[x[13] for x in res],
                   HoldObjectsInFrontTrueCount=[x[14] for x in res],
                   ShoulderBagCount=[x[15] for x in res],
                   NobagCount=[x[16] for x in res],
                   HandBagCount=[x[17] for x in res],
                   BackpackCount=[x[18] for x in res],
                   LongSleeveCount=[x[19] for x in res],
                   LongSleeveUpperLogoCount=[x[20] for x in res],
                   LongSleeveUpperSpliceCount=[x[21] for x in res],
                   ShortSleeveCount=[x[22] for x in res],
                   ShortSleeveUpperLogoCount=[x[23] for x in res],
                   LongCoatCount=[x[24] for x in res],
                   LongCoatTrousersCount=[x[25] for x in res],
                   LowerPatternCount=[x[26] for x in res],
                   ShortsCount=[x[27] for x in res],
                   SkirtDressCount=[x[28] for x in res],
                   TrousersCount=[x[29] for x in res],
                   Date=datetime.date.today().strftime('%Y%m%d'))  # 返回json格式
    #    return jsonify(ScoreAvg=res[1],
    #            MaleCount=res[2],
    #            FemaleCount=res[3],
    #            Age1860Count=res[4],
    #            AgeLess18Count=res[5],
    #            SideCount=res[6],
    #            BackCount=res[7],
    #            FrontCount=res[8],
    #            GlassesFalseCount=res[9],
    #            GlassesTrueCount=res[10])  # 返回json格式


if __name__ == "__main__":
    app.run(debug=True)
